package com.ocom.accesservice.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ocom.accesservice.vo.PersonnelYkmVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface PersonnelInfoDao extends BaseMapper<PersonnelYkmVo> {

    @Select("select i.com_id\n" +
            ",sum(case when y.p_id is null then 1 else 0 end ) as 'pullFailed'\n" +
            ",sum(case when code_color='绿' then 1 else 0 end ) as 'green'\n" +
            ",sum(case when code_color='黄' then 1 else 0 end ) as 'yellow'\n" +
            ",sum(case when code_color='红' then 1 else 0 end ) as 'red'\n" +
            ",sum(case when code_color not in ('红','黄','绿') then 1 else 0 end ) as 'other'\n" +
            ",sum(case when hs_result not in ('阴性','阳性')  then 1 else 0 end ) as 'noNucleic'\n" +
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) > 24*7) then 1 else 0 end ) as 'more7Days'\n"+
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24*3+1 and 24*7) then 1 else 0 end ) as 'within7Days'\n" +
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24*2+1 and 24*3) then 1 else 0 end ) as 'hours72'\n" +
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24+1 and 24*2) then 1 else 0 end ) as 'hours48'\n" +
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  <=24) then 1 else 0 end ) as 'hours24'\n" +
            ",sum(case when hs_result ='阳性' then 1 else 0 end ) as 'positive'\n" +
            ",sum(case when ym_num=0 then 1 else 0 end ) as 'ym0'\n" +
            ",sum(case when ym_num=1 then 1 else 0 end ) as 'ym1'\n" +
            ",sum(case when ym_num=2 then 1 else 0 end ) as 'ym2'\n" +
            ",sum(case when ym_num=3 then 1 else 0 end ) as 'ym3'\n" +
            ",sum(case when ym_num>3 then 1 else 0 end ) as 'ymMore3'\n" +
            "from personnel_info i left join personnel_ykm y \n" +
            "on i.com_id=y.com_id and  i.p_id=y.p_id\n" +
            "where i.com_id=#{comId} and i.status=0 group by i.com_id ")
    PersonnelYkmVo  dyGetUserList(Long comId);

    @Select({
            "<script>",
            "SELECT",
            "i.com_id, sum(case when code_color='绿' then 1 else 0 end ) as 'green'",
            ",sum(case when y.p_id is null then 1 else 0 end ) as 'pullFailed'" ,
            ",sum(case when code_color='黄' then 1 else 0 end ) as 'yellow'",
            ",sum(case when code_color='红' then 1 else 0 end ) as 'red'",
            ",sum(case when code_color not in ('红','黄','绿') then 1 else 0 end ) as 'other'",
            ",sum(case when hs_result not in ('阴性','阳性')   then 1 else 0 end ) as 'noNucleic'",
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) > 24*7) then 1 else 0 end ) as 'more7Days'",
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24*3+1 and 24*7) then 1 else 0 end ) as 'within7Days'",
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24*2+1 and 24*3) then 1 else 0 end ) as 'hours72'",
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) between 24+1 and 24*2) then 1 else 0 end ) as 'hours48'",
            ",sum(case when hs_result ='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  &lt;=24) then 1 else 0 end ) as 'hours24'",
            ",sum(case when hs_result ='阳性' then 1 else 0 end ) as 'positive'",
            ",sum(case when ym_num=0 then 1 else 0 end ) as 'ym0'",
            ",sum(case when ym_num=1 then 1 else 0 end ) as 'ym1'",
            ",sum(case when ym_num=2 then 1 else 0 end ) as 'ym2'",
            ",sum(case when ym_num=3 then 1 else 0 end ) as 'ym3'",
            ",sum(case when ym_num>3 then 1 else 0 end ) as 'ymMore3'",
            "from personnel_info i left join personnel_ykm y on i.com_id=y.com_id and  i.p_id=y.p_id",
            "where i.com_id=#{comId} and i.status=0 and i.dept_id in",
            "<foreach collection='typeList' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    PersonnelYkmVo  deptIdsList(@Param("comId") Long comId,@Param("typeList") List<Integer> typeList);

}
